package com.reserv.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.naming.NamingException;

import com.reserv.db.ConnectionProvider;
import com.reserv.db.JDBCManager;
import com.reserv.vo.FacilityVO;

public class FacilityDAO implements DAO<FacilityVO, Integer> {
	private Connection conn;
	private PreparedStatement pstmt;
	private ResultSet rs;
	
	private static FacilityDAO instance = new FacilityDAO();
	public static FacilityDAO getInstance() {
		return instance != null ? instance : new FacilityDAO();
	}

	@Override	//시설정보 등록
	public void createVO(FacilityVO facil) { }

	@Override	//시절정보 가져오기
	public FacilityVO getVO(Integer fno) { 
		String sql = "SELECT * FROM facility WHERE fno=?";
		FacilityVO f = null;
		try {
			conn = ConnectionProvider.getConnection();
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setInt(1, fno);
			
			rs = pstmt.executeQuery();
			while(rs.next()) {
				f = new FacilityVO();
				
				f.setFno(rs.getInt(1));
				f.setName(rs.getString(2));
				f.setAddr(rs.getString(3));
				f.setImg(rs.getString(4));
				f.setDirect(rs.getString(5));
				f.setAvgGrade(rs.getInt(6));
				f.setRegDate(rs.getTimestamp(7));
			}
		} catch(SQLException | NamingException e) {
			e.printStackTrace();
		} finally {
			JDBCManager.closeAll(conn, pstmt, rs);
		}
		return f; 
	}

	@Override	//모든 시설정보 가져오기
	public List<FacilityVO> getListVOs() { return null; }
	//특정 검색조건의 시설정보 가져오기
	public List<FacilityVO> getListVOs(String findkey, String findVal) { return null; }

	@Override	//시설정보 수정
	public void updateVO(FacilityVO facil) { }

	@Override	//시설정보 삭제
	public void deleteVO(Integer facilNo) { }
	
	public List<FacilityVO> getGuListVOs(String type) { 
		List<FacilityVO> list = new ArrayList<FacilityVO>();
		FacilityVO f = null;
		try {
			conn = ConnectionProvider.getConnection();
			//pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, type);
			
			rs = pstmt.executeQuery();
			while(rs.next()) {
				f = new FacilityVO();
				
				list.add(f);
			}
		} catch (NamingException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list; 
	}
	public List<FacilityVO> getFacilNameListVOs(String type) {
		String sql = "select facilName from facility where facilAddr=?";
		List<FacilityVO> list = new ArrayList<FacilityVO>();
		FacilityVO f = null;
		try {
			conn = ConnectionProvider.getConnection();
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, type);
			
			rs = pstmt.executeQuery();
			while(rs.next()) {
				f = new FacilityVO();
				
				
				list.add(f);
			}
		} catch (NamingException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	public ArrayList<FacilityVO> getListVOs(int startRow, int num) {
		
		String Sql = "SELECT * FROM facility limit ?, ?;";
		ArrayList<FacilityVO> facilityList = null;
		
		try{
			conn = ConnectionProvider.getConnection();
			pstmt = conn.prepareStatement(Sql);
			pstmt.setInt(1, startRow);
			pstmt.setInt(2, num);
			
			rs = pstmt.executeQuery();
			
			if(rs.next()) {
				
				facilityList = new ArrayList<FacilityVO>();
				
				do {
					FacilityVO facility = new FacilityVO();
					
					
					facilityList.add(facility);
					
				} while(rs.next());
			}
			
		} catch(Exception e) {
			System.out.println("FacilityDao - getListVOs()");
			e.printStackTrace();
		} finally {
			
			JDBCManager.closeAll(conn, pstmt, rs);
		}
		return facilityList;
	}
	//시설 갯수 가져오기
		public int getFacilityCount() {
			
			String sqlCount = "SELECT COUNT(*) FROM facility;";
			int count = 0;
			
			try{
				conn = ConnectionProvider.getConnection();
				pstmt = conn.prepareStatement(sqlCount);
				
				rs = pstmt.executeQuery();
				
				while(rs.next()) {
					count = rs.getInt(1);
				}
				
			} catch(Exception e) {
				System.out.println("FacilityDao - getFacilityCount()");
				e.printStackTrace();
				
			} finally {
				JDBCManager.closeAll(conn, pstmt, rs);
			}
			return count;
		}
}
